﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class ExportDeclarationsDA : IExportDeclarationsDA
    {
        private string BUSINESS_TYPE = "@BUSINESS_TYPE";
        private string CASE_CNT = "@CASE_CNT";
        private string COMPANY_DECLARATIONGS_ID = "@COMPANY_DECLARATIONGS_ID";
        private string CREATED_BY = "@CREATED_BY";
        private string DECLARATIONS_DATE = "@DECLARATIONS_DATE";
        private string DECLARATIONS_ID = "@DECLARATIONS_ID";
        private string DECLARATIONS_TYPE = "@DECLARATIONS_TYPE";
        private string EXTENDED_PRICE = "@EXTENDED_PRICE";
        private string IMPORT_DATE = "@IMPORT_DATE";
        private string NTOTAL_ET_WGT = "@NTOTAL_ET_WGT";
        private string ORDER_ID = "@ORDER_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string OWNER_ORDER_ID = "@OWNER_ORDER_ID";
        private string PALLET = "@PALLET";
        private string REAMRK = "@REAMRK";
        private string RECORD_DATE = "@RECORD_DATE";
        private string SQL_CHECK_EXPORT_DECLARATIONS_ID_UNIQUE = " SELECT COUNT(1) FROM @_@EXPORT_DECLARATIONS WHERE DECLARATIONS_ID = @DECLARATIONS_ID ";
        private string SQL_DELETE_EXPORT_DECLARATIONS = " DELETE FROM @_@EXPORT_DECLARATIONS WHERE DECLARATIONS_ID = @DECLARATIONS_ID ";
        private string SQL_INSERT_EXPORT_DECLARATIONS = " INSERT INTO @_@EXPORT_DECLARATIONS ( WH_ID, DECLARATIONS_ID, DECLARATIONS_TYPE, ORDER_ID, DECLARATIONS_DATE, BUSINESS_TYPE, STATUS, OWNER_ID, COMPANY_DECLARATIONGS_ID, VERIFICATION_FORM_ID, OWNER_ORDER_ID, IMPORT_DATE, RECORD_DATE, TOTAL_CUBE, TOTAL_GROSS_WGT, NTOTAL_ET_WGT, EXTENDED_PRICE, CASE_CNT, PALLET, REAMRK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @DECLARATIONS_ID, @DECLARATIONS_TYPE, @ORDER_ID, @DECLARATIONS_DATE, @BUSINESS_TYPE, @STATUS, @OWNER_ID, @COMPANY_DECLARATIONGS_ID, @VERIFICATION_FORM_ID, @OWNER_ORDER_ID, @IMPORT_DATE, @RECORD_DATE, @TOTAL_CUBE, @TOTAL_GROSS_WGT, @NTOTAL_ET_WGT, @EXTENDED_PRICE, @CASE_CNT, @PALLET, @REAMRK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_EXPORT_DECLARATIONS = " SELECT  EDS.ROWID, EDS.WH_ID, EDS.DECLARATIONS_ID, EDS.DECLARATIONS_TYPE, EDS.ORDER_ID,EDS.DECLARATIONS_DATE, EDS.BUSINESS_TYPE, EDS.STATUS, EDS.OWNER_ID, EDS.COMPANY_DECLARATIONGS_ID, EDS.VERIFICATION_FORM_ID, EDS.OWNER_ORDER_ID, EDS.IMPORT_DATE, EDS.RECORD_DATE, EDS.TOTAL_CUBE, EDS.TOTAL_GROSS_WGT, EDS.NTOTAL_ET_WGT, EDS.EXTENDED_PRICE, EDS.CASE_CNT,EDS.PALLET, EDS.REAMRK, EDS.TS, EDS.CREATED_BY, EDS.CREATED_DATE, EDS.UPDATED_BY, EDS.UPDATED_DATE,T.[TEXT] AS STATUS_D,T1.[TEXT] AS DECLARATIONS_TYPE_D  FROM  @_@EXPORT_DECLARATIONS EDS  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = EDS.STATUS AND SCD.SYS_CODE_TYPE = 'CustomsStatus' LEFT JOIN sysadm.TRANSLATION T ON 'CustomsStatus.'+EDS.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN @_@SYS_CODE_DETAIL SCD1 ON SCD1.SYS_CODE = EDS.DECLARATIONS_TYPE AND SCD.SYS_CODE_TYPE = 'CustomsType' LEFT JOIN sysadm.TRANSLATION T1 ON 'CustomsType.'+EDS.DECLARATIONS_TYPE = T1.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T1.LANG+'-'+T1.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON EDS.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_EXPORT_DECLARATIONS = " UPDATE @_@EXPORT_DECLARATIONS SET WH_ID = @WH_ID, DECLARATIONS_TYPE = @DECLARATIONS_TYPE, ORDER_ID = @ORDER_ID, DECLARATIONS_DATE = @DECLARATIONS_DATE, BUSINESS_TYPE = @BUSINESS_TYPE, STATUS = @STATUS, OWNER_ID = @OWNER_ID, COMPANY_DECLARATIONGS_ID = @COMPANY_DECLARATIONGS_ID, VERIFICATION_FORM_ID = @VERIFICATION_FORM_ID, OWNER_ORDER_ID = @OWNER_ORDER_ID, IMPORT_DATE = @IMPORT_DATE, RECORD_DATE = @RECORD_DATE, TOTAL_CUBE = @TOTAL_CUBE, TOTAL_GROSS_WGT = @TOTAL_GROSS_WGT, NTOTAL_ET_WGT = @NTOTAL_ET_WGT, EXTENDED_PRICE = @EXTENDED_PRICE, CASE_CNT = @CASE_CNT, PALLET = @PALLET, REAMRK = @REAMRK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE DECLARATIONS_ID = @DECLARATIONS_ID  ";
        private string SQL_UPDATE_EXPORT_DECLARATIONS_STATUS = " UPDATE @_@EXPORT_DECLARATIONS SET  STATUS = @STATUS , VERIFICATION_FORM_ID = '' , UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE DECLARATIONS_ID = @DECLARATIONS_ID  ";
        private string SQL_UPDATE_EXPORT_DECLARATIONS_WRITTENOFF_STATUS = " UPDATE @_@EXPORT_DECLARATIONS SET VERIFICATION_FORM_ID = @VERIFICATION_FORM_ID, STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE DECLARATIONS_ID = @DECLARATIONS_ID  ";
        private string STATUS = "@STATUS";
        private string TOTAL_CUBE = "@TOTAL_CUBE";
        private string TOTAL_GROSS_WGT = "@TOTAL_GROSS_WGT";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VERIFICATION_FORM_ID = "@VERIFICATION_FORM_ID";
        private string WH_ID = "@WH_ID";

        public bool CheckExportDeclarationsIDUnique(string exportDeclarationsID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportDeclarationsID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_EXPORT_DECLARATIONS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckExportDeclarationsIDUnique(DataBase dataBase, DbTransaction tran, string exportDeclarationsID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportDeclarationsID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_EXPORT_DECLARATIONS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteExportDeclarations(List<string> exportDeclarationsID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < exportDeclarationsID.Count; i++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportDeclarationsID[i]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_EXPORT_DECLARATIONS.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteExportDeclarations(DataBase dataBase, DbTransaction tran, List<string> exportDeclarationsID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < exportDeclarationsID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportDeclarationsID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_EXPORT_DECLARATIONS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllExportDeclarations(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllExportDeclarations(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public ExportDeclarationsInfo GetExportDeclarationsByID(string exportdeclarationsID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS.Replace("@_@", whLoginID) + " WHERE DECLARATIONS_ID = @DECLARATIONS_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportdeclarationsID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ExportDeclarationsInfo exportDeclarationsInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    exportDeclarationsInfo = new ExportDeclarationsInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["ORDER_ID"].ToString(), (reader["DECLARATIONS_DATE"].ToString() != "") ? DateTime.Parse(reader["DECLARATIONS_DATE"].ToString()) : (CS00003 = new DateTime()), reader["BUSINESS_TYPE"].ToString(), reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["COMPANY_DECLARATIONGS_ID"].ToString(), reader["VERIFICATION_FORM_ID"].ToString(), reader["OWNER_ORDER_ID"].ToString(), (reader["IMPORT_DATE"].ToString() != "") ? DateTime.Parse(reader["IMPORT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECORD_DATE"].ToString() != "") ? DateTime.Parse(reader["RECORD_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["TOTAL_CUBE"].ToString() != "") ? decimal.Parse(reader["TOTAL_CUBE"].ToString()) : 0M, (reader["TOTAL_GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["TOTAL_GROSS_WGT"].ToString()) : 0M, (reader["NTOTAL_ET_WGT"].ToString() != "") ? decimal.Parse(reader["NTOTAL_ET_WGT"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, reader["REAMRK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return exportDeclarationsInfo;
        }

        public ExportDeclarationsInfo GetExportDeclarationsByID(DataBase dataBase, DbTransaction tran, string exportdeclarationsID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS.Replace("@_@", whLoginID) + " WHERE DECLARATIONS_ID = @DECLARATIONS_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportdeclarationsID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ExportDeclarationsInfo exportDeclarationsInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                exportDeclarationsInfo = new ExportDeclarationsInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["ORDER_ID"].ToString(), (reader["DECLARATIONS_DATE"].ToString() != "") ? DateTime.Parse(reader["DECLARATIONS_DATE"].ToString()) : (CS00003 = new DateTime()), reader["BUSINESS_TYPE"].ToString(), reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["COMPANY_DECLARATIONGS_ID"].ToString(), reader["VERIFICATION_FORM_ID"].ToString(), reader["OWNER_ORDER_ID"].ToString(), (reader["IMPORT_DATE"].ToString() != "") ? DateTime.Parse(reader["IMPORT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECORD_DATE"].ToString() != "") ? DateTime.Parse(reader["RECORD_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["TOTAL_CUBE"].ToString() != "") ? decimal.Parse(reader["TOTAL_CUBE"].ToString()) : 0M, (reader["TOTAL_GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["TOTAL_GROSS_WGT"].ToString()) : 0M, (reader["NTOTAL_ET_WGT"].ToString() != "") ? decimal.Parse(reader["NTOTAL_ET_WGT"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, reader["REAMRK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return exportDeclarationsInfo;
        }

        public DataSet GetExportDeclarationsByQueryList(List<string> sqlWhere, ExportDeclarationsQueryEntity exportdeclarationsQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!exportdeclarationsQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, exportdeclarationsQuery.CurrentPage, exportdeclarationsQuery.PageSize, exportdeclarationsQuery.SortField, exportdeclarationsQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetExportDeclarationsByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, ExportDeclarationsQueryEntity exportdeclarationsQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, exportdeclarationsQuery.CurrentPage, exportdeclarationsQuery.PageSize, exportdeclarationsQuery.SortField, exportdeclarationsQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertExportDeclarations(ExportDeclarationsInfo exportDeclarationsInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                exportDeclarationsInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ExportDeclarations_Parameters(exportDeclarationsInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_EXPORT_DECLARATIONS.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertExportDeclarations(DataBase dataBase, DbTransaction tran, ExportDeclarationsInfo exportDeclarationsInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                exportDeclarationsInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ExportDeclarations_Parameters(exportDeclarationsInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_EXPORT_DECLARATIONS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_ExportDeclarations_Parameters(ExportDeclarationsInfo exportDeclarationsInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DECLARATIONS_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DECLARATIONS_TYPE, SqlDbType.VarChar, 10), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DECLARATIONS_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.BUSINESS_TYPE, SqlDbType.VarChar, 10), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.COMPANY_DECLARATIONGS_ID, SqlDbType.VarChar, 50), new SqlParameter(this.VERIFICATION_FORM_ID, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_ORDER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.IMPORT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.RECORD_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.TOTAL_CUBE, SqlDbType.Decimal, 13), new SqlParameter(this.TOTAL_GROSS_WGT, SqlDbType.Decimal, 13), new SqlParameter(this.NTOTAL_ET_WGT, SqlDbType.Decimal, 13), 
                new SqlParameter(this.EXTENDED_PRICE, SqlDbType.Decimal, 13), new SqlParameter(this.CASE_CNT, SqlDbType.Decimal, 13), new SqlParameter(this.PALLET, SqlDbType.Decimal, 13), new SqlParameter(this.REAMRK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(exportDeclarationsInfo.WhID))
            {
                paramArray[0].Value = exportDeclarationsInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = exportDeclarationsInfo.DeclarationsID;
            paramArray[2].Value = exportDeclarationsInfo.DeclarationsType;
            paramArray[3].Value = exportDeclarationsInfo.OrderID;
            paramArray[4].Value = exportDeclarationsInfo.DeclarationsDate;
            paramArray[5].Value = exportDeclarationsInfo.BusinessType;
            paramArray[6].Value = exportDeclarationsInfo.Status;
            paramArray[7].Value = exportDeclarationsInfo.OwnerID;
            if (!string.IsNullOrEmpty(exportDeclarationsInfo.CompanyDeclarationgsID))
            {
                paramArray[8].Value = exportDeclarationsInfo.CompanyDeclarationgsID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsInfo.VerificationFormID))
            {
                paramArray[9].Value = exportDeclarationsInfo.VerificationFormID;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsInfo.OwnerOrderID))
            {
                paramArray[10].Value = exportDeclarationsInfo.OwnerOrderID;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            DateTime CS00003 = new DateTime();
            if (exportDeclarationsInfo.ImportDate != CS00003)
            {
                paramArray[11].Value = exportDeclarationsInfo.ImportDate;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (exportDeclarationsInfo.RecordDate != new DateTime())
            {
                paramArray[12].Value = exportDeclarationsInfo.RecordDate;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            paramArray[13].Value = exportDeclarationsInfo.TotalCube;
            paramArray[14].Value = exportDeclarationsInfo.TotalGrossWgt;
            paramArray[15].Value = exportDeclarationsInfo.NtotalEtWgt;
            paramArray[0x10].Value = exportDeclarationsInfo.ExtendedPrice;
            paramArray[0x11].Value = exportDeclarationsInfo.CaseCnt;
            paramArray[0x12].Value = exportDeclarationsInfo.Pallet;
            if (!string.IsNullOrEmpty(exportDeclarationsInfo.Reamrk))
            {
                paramArray[0x13].Value = exportDeclarationsInfo.Reamrk;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            paramArray[20].Value = exportDeclarationsInfo.CreatedBy;
            paramArray[0x15].Value = exportDeclarationsInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateExportDeclarations(ExportDeclarationsInfo exportDeclarationsInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                exportDeclarationsInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ExportDeclarations_Parameters(exportDeclarationsInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_EXPORT_DECLARATIONS.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateExportDeclarations(DataBase dataBase, DbTransaction tran, ExportDeclarationsInfo exportDeclarationsInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                exportDeclarationsInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ExportDeclarations_Parameters(exportDeclarationsInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_EXPORT_DECLARATIONS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateExportDeclarationsStatusByID(DataBase dataBase, DbTransaction tran, string declarationsID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", declarationsID), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_EXPORT_DECLARATIONS_STATUS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateExportDeclarationsStatusByID(DataBase dataBase, DbTransaction tran, string declarationsID, string verificationFormID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", declarationsID), new SqlParameter("@VERIFICATION_FORM_ID", verificationFormID), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_EXPORT_DECLARATIONS_WRITTENOFF_STATUS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

